
DROP TABLE AUCTION;

CREATE TABLE AUCTION(
	AUCTION_NO number PRIMARY KEY,
	AUCTION_NAME VARCHAR2(200) NOT NULL,
	PRODUCT_NAME VARCHAR2(60) NOT NULL,
	CATEGORY_NAME VARCHAR2(30) NOT NULL,
	SITE_URL VARCHAR2(200), 
	SALE_LOCATION VARCHAR2(30)  NOT NULL, -- 판매지역
	USE_START_DATE VARCHAR2(14)  NOT NULL, -- 사용 시작일
	USE_END_DATE VARCHAR2(14)  NOT NULL, -- 사용 마감일
	REGISTER_DATE VARCHAR2(14)  NOT NULL, -- 등록일
	UPDATE_DATE VARCHAR2(14), -- 수정일
	DELETE_DATE VARCHAR2(14),
	WANT_PRICE NUMBER(10)  NOT NULL, -- 희망가
	ORIGINAL_PRICE NUMBER(10)  NOT NULL, -- 원가
	COUPON_PRICE NUMBER(10)  NOT NULL, -- 쿠폰가격
	QUANTITY NUMBER(4)  NOT NULL, -- 개수
	MEMBER_ID VARCHAR2(18)  NOT NULL, 
	FILE_URL VARCHAR2(50), -- 파일명 또는 파일 URL
	AUCTION_CONTENT LONG,
	CONSTRAINT AUCTION_FK FOREIGN KEY(MEMBER_ID) REFERENCES MEMBER(MEMBER_ID) ON DELETE CASCADE,
	CONSTRAINT AUCTION_FK_CATEGORY FOREIGN KEY(CATEGORY_NAME) REFERENCES CATEGORY(CATEGORY_NAME) ON DELETE CASCADE
);

SELECT * FROM AUCTION ORDER BY AUCTION_NO DESC;

alter table Auction modify (Auction_no number);

ALTER TABLE AUCTION ADD(AUCTION_NAME VARCHAR2(200) NOT NULL)

CREATE SEQUENCE AUCTION_NO_SEQ;

SELECT AUCTION_NO_SEQ.NEXTVAL FROM DUAL;


 SELECT 	
	    		AUCTION.AUCTION_NO,
	    		AUCTION.AUCTION_NAME,
			    AUCTION.PRODUCT_NAME,
			    AUCTION.CATEGORY_NAME,
			    AUCTION.SITE_URL,
				AUCTION.SALE_LOCATION,
				AUCTION.USE_START_DATE,
				AUCTION.USE_END_DATE,
				AUCTION.REGISTER_DATE,
				AUCTION.UPDATE_DATE,
				AUCTION.DELETE_DATE,
				AUCTION.WANT_PRICE,
				AUCTION.ORIGINAL_PRICE,
				AUCTION.COUPON_PRICE,
				AUCTION.QUANTITY,
				MEMBER.MEMBER_ID,
				AUCTION.FILE_URL,
				AUCTION.AUCTION_CONTENT,
				MEMBER.REGISTER_NUMBER1,
				MEMBER.REGISTER_NUMBER2,
				MEMBER.PASSWORD,
				MEMBER.NAME,
				MEMBER.ADDRESS,
				MEMBER.PHONE,
				MEMBER.PHOTO,
				MEMBER.MILEAGE,
				MEMBER.SEQ,
				MEMBER.CREDIT,
				MEMBER.IS_VALIDATE,
				MEMBER.EMAIL,
				MEMBER.CREDIT_SCORE				
		FROM	AUCTION,
				MEMBER
		WHERE	AUCTION.MEMBER_ID = 2222
		AND		MEMBER.MEMBER_ID = AUCTION.MEMBER_ID
		
		
		
		
		
		
		
		
		
		
		SELECT	COUNT(AUCTION_NO)
		FROM	AUCTION,
				MEMBER
		WHERE	MEMBER.MEMBER_ID = AUCTION.MEMBER_ID
		AND		MEMBER.MEMBER_ID = '2222'
		
		
		
		
		
		
		
		
		
		
		SELECT	PAGE,
    		AUCTION_NO,
	    	AUCTION_NAME,
			PRODUCT_NAME,
			CATEGORY_NAME,
			SITE_URL,
			SALE_LOCATION,
			USE_START_DATE,
			USE_END_DATE,
			REGISTER_DATE,
			UPDATE_DATE,
			DELETE_DATE,
			WANT_PRICE,
			ORIGINAL_PRICE,
			COUPON_PRICE,
			QUANTITY,
			MEMBER_ID,
			FILE_URL,
			AUCTION_CONTENT,
			REGISTER_NUMBER1,
			REGISTER_NUMBER2,
			PASSWORD,
			NAME,
			ADDRESS,
			PHONE,
			PHOTO,
			MILEAGE,
			SEQ,
			CREDIT,
			IS_VALIDATE,
			EMAIL,
			CREDIT_SCORE			
    FROM	(
    		SELECT	ceil(rownum/10) PAGE,
    				AUCTION_NO,
	   			 	AUCTION_NAME,
					PRODUCT_NAME,
					CATEGORY_NAME,
					SITE_URL,
					SALE_LOCATION,
					USE_START_DATE,
					USE_END_DATE,
					REGISTER_DATE,
					UPDATE_DATE,
					DELETE_DATE,
					WANT_PRICE,
					ORIGINAL_PRICE,
					COUPON_PRICE,
					QUANTITY,
					MEMBER_ID,
					FILE_URL,
					AUCTION_CONTENT,
					REGISTER_NUMBER1,
					REGISTER_NUMBER2,
					PASSWORD,
					NAME,
					ADDRESS,
					PHONE,
					PHOTO,
					MILEAGE,
					SEQ,
					CREDIT,
					IS_VALIDATE,
					EMAIL,
					CREDIT_SCORE	
    		FROM	(
    				SELECT	AUCTION.AUCTION_NO,
					    	AUCTION.AUCTION_NAME,
							AUCTION.PRODUCT_NAME,
							AUCTION.CATEGORY_NAME,
							AUCTION.SITE_URL,
							AUCTION.SALE_LOCATION,
							AUCTION.USE_START_DATE,
							AUCTION.USE_END_DATE,
							AUCTION.REGISTER_DATE,
							AUCTION.UPDATE_DATE,
							AUCTION.DELETE_DATE,
							AUCTION.WANT_PRICE,
							AUCTION.ORIGINAL_PRICE,
							AUCTION.COUPON_PRICE,
							AUCTION.QUANTITY,
							MEMBER.MEMBER_ID,
							AUCTION.FILE_URL,
							AUCTION.AUCTION_CONTENT,
							MEMBER.REGISTER_NUMBER1,
							MEMBER.REGISTER_NUMBER2,
							MEMBER.PASSWORD,
							MEMBER.NAME,
							MEMBER.ADDRESS,
							MEMBER.PHONE,
							MEMBER.PHOTO,
							MEMBER.MILEAGE,
							MEMBER.SEQ,
							MEMBER.CREDIT,
							MEMBER.IS_VALIDATE,
							MEMBER.EMAIL,
							MEMBER.CREDIT_SCORE		
    				FROM 	AUCTION,
    						MEMBER
    				WHERE 	MEMBER.MEMBER_ID = AUCTION.MEMBER_ID
    				AND		AUCTION.AUCTION_NAME LIKE '%1%'
    				ORDER BY AUCTION_NO DESC
    				)
    		) 
    WHERE PAGE = 1